from flask import Flask, render_template, request, redirect, url_for, jsonify
import sqlite3
import os

app = Flask(__name__)
app.config['SECRET_KEY'] = 'your_secret_key'
app.config['UPLOAD_FOLDER'] = 'static/images'

# 创建数据库和表
def init_db():
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('''
              

        CREATE TABLE IF NOT EXISTS menus (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            image TEXT NOT NULL,
            recipe TEXT NOT NULL,
            price REAL NOT NULL
        )
    ''')
    c.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            menu_id INTEGER NOT NULL,
            FOREIGN KEY (menu_id) REFERENCES menus (id)
        )
    ''')
    conn.commit()
    conn.close()

@app.route('/')
def index():
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('SELECT * FROM menus')
    menus = c.fetchall()
    conn.close()
    return render_template('index.html', menus=menus)

@app.route('/add_menu', methods=['POST'])
def add_menu():
    name = request.form['name']
    image = request.files['image']
    recipe = request.form['recipe']
    price = request.form.get('price', 0.0)
    
    if image and allowed_file(image.filename):
        image_path = os.path.join(app.config['UPLOAD_FOLDER'], image.filename)
        image.save(image_path)
        
        conn = sqlite3.connect('menus.db')
        c = conn.cursor()
        c.execute('INSERT INTO menus (name, image, recipe, price) VALUES (?, ?, ?, ?)', (name, image_path, recipe, price))
        conn.commit()
        conn.close()
        
        return redirect(url_for('index'))
    else:
        return "Invalid file type", 400

@app.route('/edit_menu', methods=['POST'])
def edit_menu():
    menu_id = request.form['id']
    name = request.form['name']
    image = request.files['image']
    recipe = request.form['recipe']
    price = request.form.get('price', 0.0) 
    
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    
    if image and allowed_file(image.filename):
        image_path = os.path.join(app.config['UPLOAD_FOLDER'], image.filename)
        image.save(image_path)
        c.execute('UPDATE menus SET name = ?, image = ?, recipe = ?, price = ? WHERE id = ?', (name, image_path, recipe, price, menu_id))
    else:
        c.execute('UPDATE menus SET name = ?, recipe = ?, price = ? WHERE id = ?', (name, recipe, price, menu_id))
    
    conn.commit()
    conn.close()
    
    return redirect(url_for('index'))

@app.route('/order_menu/<int:menu_id>', methods=['POST'])
def order_menu(menu_id):
    print("Received order request for menu_id:", menu_id)
    
    try:
        conn = sqlite3.connect('menus.db')
        c = conn.cursor()
        c.execute('INSERT INTO orders (menu_id) VALUES (?)', (menu_id,))
        conn.commit()
        conn.close()
        
        return jsonify({'status': 'success'}), 200
    except Exception as e:
        print("Error inserting order:", str(e))
        return jsonify({'status': 'failure', 'message': str(e)}), 500

@app.route('/delete_ordered_menu/<int:order_id>', methods=['POST'])
def delete_ordered_menu(order_id):
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('DELETE FROM orders WHERE id = ?', (order_id,))
    conn.commit()
    conn.close()
    
    return jsonify({'status': 'success'})

@app.route('/delete_menu/<int:menu_id>', methods=['POST'])
def delete_menu(menu_id):
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('DELETE FROM menus WHERE id = ?', (menu_id,))
    conn.commit()
    conn.close()
    
    return redirect(url_for('index'))

@app.route('/ordered_menus')
def ordered_menus():
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('''
        SELECT orders.id AS order_id, menus.name, menus.price
        FROM orders
        JOIN menus ON orders.menu_id = menus.id
    ''')
    orders = c.fetchall()
    conn.close()
    
    ordered_menus = [{'order_id': order[0], 'name': order[1], 'price': order[2]} for order in orders]
    return render_template('ordered_menus.html', orders=ordered_menus)

@app.route('/get_ordered_menus', methods=['GET'])
def get_ordered_menus():
    conn = sqlite3.connect('menus.db')
    c = conn.cursor()
    c.execute('''
        SELECT orders.id AS order_id, menus.name, menus.price
        FROM orders
        JOIN menus ON orders.menu_id = menus.id
    ''')
    orders = c.fetchall()
    conn.close()
    
    ordered_menus = [{'order_id': order[0], 'name': order[1], 'price': order[2]} for order in orders]
    return jsonify({'orders': ordered_menus})

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in {'png', 'jpg', 'jpeg', 'gif'}

if __name__ == '__main__':
    init_db()
    app.run(debug=True, host='0.0.0.0')